跳到主要内容

MySQL 学习（12）约束与触发器

数据完整性约束​

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。

当前几乎所有的关系型数据库都提供了约束（constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。

一般来说，数据完整性有以下三种形式：

1、实体完整性 保证表中有一个主键。在 InnoDB 存储引擎表中，用户可以通过定义 Primary Key 或 Unique Key 约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。

2、域完整性 保证数据每列的值满足特定的条件。在 InnoDB 存储引擎表中，域完整性可以通过以下几种途径来保证：

  • 选择合适的数据类型确保一个数据值满足特定条件。
  • 外键（Foreign Key）约束。
  • 编写触发器。
  • 还可以考虑用 DEFAULT 约束作为强制域完整性的一个方面。

3、参照完整性 保证两张表之间的关系。InnoDB 存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于 InnoDB 存储引擎本身而言，提供了以下几种约束：

  • Primary Key
  • Unique Key
  • Foreign Key
  • Default
  • NOT NULL

约束的建立与查找​

约束的创建可以采用以下两种方式：

  • 表建立时就进行约束定义
  • 利用 ALTER TABLE 命令来进行创建约束

对 Unique Key（唯一索引）的约束，用户还可以通过命令 CREATE UNIQUE INDEX 来建立。对于主键约束而言，其默认约束名为 PRIMARY。而对于 Unique Key 约束而言，默认约束名和列名一样，当然也可以人为指定 Unique Key 约束的名字。

Foreign Key 约束似乎会有一个比较神秘的默认名称。

下面是一个简单的创建表的语句，表上有一个主键和一个唯一键：

create table t_user(
    id int(10),
    name varchar(32),
    primary key(id),
    unique key(name)
);

具体如何创建就不讲了~

索引和约束的区别​

在前面的小节中已经看到 Primary Key 和 Unique Key 的约束，有人不禁会问：这不就是通常创建索引的方法吗？那约束和索引有什么区别呢？

的确，当用户创建了一个唯一索引就创建了一个唯一的约束。

但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

对错误数据的约束​

在某些默认设置下，MySQL 数据库允许非法的或不正确的数据的插人或更新，又或者可以在数据库内部将其转化为一个合法的值，如向 NOT NULL 的字段插入一个 NULL 值，MySQL 数据库会将其更改为 0 再进行插人，因此数据库本身没有对数据的正确性进行约束。

例如：

create table a
(
    id   int  not null,
    data Date not null
)

-- 如果插入非 date 的数据进去它并不会报错，只会抛出警告
-- 如果想要报错而不是警告得使用 sql_mode 参数来严格审核输入的参数

set sql_mode = 'STRICT_TRANS_TABLES';

ENUM 和 SET 约束​

MySQL 数据库不支持传统的 CHECK 约束，但是通过 ENUM 和 SET 类型可以解决部分这样的约束需求。

例如表上有一个性别类型，规定域的范围只能是 male 或 female,在这种情况下用户可以通过 ENUM 类型来进行约束。

CREATE TABLE a (
  id INT,
  sex ENUM('male', 'female')
);

INSERT INTO a SELECT 1, 'female';

INSERT INTO a SELECT 2, 'bi';

可以看到，在上述例子中对第二条记录的插人依然是报了警告。因此如果想实现 CHECK 约束，还需要配合设置参数 sql_mode

SET sql_mode = 'STRICT_TRANS_TABLES';

这次对非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统 CHECK 约束支持的连续值的范围约束或更复杂的约束，ENUM 和 SET 类型还是无能为力，这时用户需要通过触发器来实现对于值域的约束。

触发器与约束器​

通过前面小节的介绍，用户已经知道完整性约束通常也可以使用触发器来实现，因此在了解数据完整性前先对触发器来做一个了解。

触发器的作用是在执行 INSERT、DELETE 和 UPDATE 命令之前或之后自动调用 SQL 命令或存储过程。MySQL 5.0 对触发器的实现还不是非常完善，限制比较多，而从 MySQL 5.1 开始触发器已经相对稳定，功能也较之前有了大幅的提高。

创建触发器的命令是 CREATE TRIGGER，只有具备 Super 权限的 MySQL 数据库用户才可以执行这条命令：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

最多可以为一个表建立 6 个触发器，即分别为 INSERT、UPDATE、DELETE 的 BEFORE 和 AFTER 各定义一个。BEFORE 和 AFTER 代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。

当前 MySQL 数据库只支持 FOR EACH ROW 的触发方式，即按每行记录进行触发，不支持像 DB2 的 FOR EACH STATEMENT 的触发方式。

通过触发器，用户可以实现 MySQL 数据库本身并不支持的一些特性，如对于传统 CHECK 约束的支持，物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。

示例：创建学生信息表 tb_student 时，将年龄（age）的值设置在 1 至 100 之间的数值。

-- 学生信息表
CREATE TABLE tb_student
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30),
    age INT NOT NULL CHECK(age>0 AND age<100)
);

上述示例中，虽然给年龄（age）字段设置了 CHECK 检查约束，但是仍然可以往该数据表中添加不符合要求的年龄数组（原因上面说了），例如：-1 或者 120。

解决方法：使用触发器实现 CHECK 检查约束的功能。

1、创建 tb_student（学生信息表）。

-- 创建学生信息表
CREATE TABLE tb_student
(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30),
    age INT NOT NULL
);

2、创建检查年龄（age）字段是否有效的触发器。

-- 创建触发器
CREATE TRIGGER trg_tb_student_insert_check BEFORE INSERT
ON tb_student FOR EACH ROW
BEGIN
    DECLARE msg varchar(100);
 
    IF NEW.age <= 0 OR NEW.age >= 100 
    THEN
        SET msg = CONCAT('您输入的年龄值：',NEW.age,' 为无效的年龄，请输入0到100以内的有效数字。');
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
    END IF;
END;

3、编写测试语句。

INSERT INTO tb_student(name,age) VALUES('测试数据~',120);

这时就会抛出错误

外键约束​

外键用来保证参照完整性，MySQL 数据库的 MyISAM 存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。而 InnoDB 存储引擎则完整支持外键约束。

一般来说，称被引用的表为父表，引用的表称为子表。外键定义时的 ON DELETE 和 ON UPDATE 表示在对父表进行 DELETE 和 UPDATE 操作时，对子表所做的操作，可定义的子表操作有：

  • CASCADE
  • SET NULL
  • NO ACTION
  • RESTRICT

1、CASCADE 表示当父表发生 DELETE 或 UPDATE 操作时，对相应的子表中的数据也进行 DELETE 或 UPDATE 操作。SET NULL 表示当父表发生 DELETE 或 UPDATE 操作时，相应的子表中的数据被更新为 NULL 值，但是子表中相对应的列必须允许为 NULL 值。NO ACTION 表示当父表发生 DELETE 或 UPDATE 操作时，抛出错误，不允许这类操作发生。

2、RESTRICT 表示当父表发生 DELETE 或 UPDATE 操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定 ON DELETE 或 ON UPDATE，RESTRICT 就是默认的外键设置。

在其他数据库中，如 Oracle 数据库，有一种称为延时检查 deferred check 的外键约束，即检查在 SQL 语句运行完成后再进行。而目前 MySQL 数据库的外键约束都是即时检查（immediatecheck）

因此从上面的定义可以看出，在 MySQL 数据库中 NO ACTION 和 RESTRICT 的功能是相同的。

对于参照完整性约束，外键能起到一个非常好的作用。但是 对于数据的导人操作时，外键往往导致在外键约束的检查上花费大量时间。

因为 MySQL 数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导人过程中忽视外键的检查，如：

-- 导入大量数据前先关闭外键检查
SET foreign_key_checks = 0;
LOAD Data ...
-- 导入后再开启
SET foreign_key_checks = 1;